#!/usr/bin/python3
# coding=utf-8

import sys
import akshare as ak
import pandas as pd
import psycopg2
import time

create_table = '''CREATE TABLE IF NOT EXISTS stock_datas.stock_{}_{}
(
    id SERIAL PRIMARY KEY NOT NULL,
    date date NOT NULL UNIQUE,
    open real NOT NULL,
    close real NOT NULL,
    hight real NOT NULL,
    low real NOT NULL,
    volume double precision DEFAULT 0.0,
    volume_quota double precision DEFAULT 0.0,
    vibration real,
    rise_fall_rate real DEFAULT 0.0,
    rise_fall_quota real DEFAULT 0.0,
    turnover_rate real
);'''

insert_sql = '''INSERT INTO stock_datas.stock_qfq_{}(
	date, open, close, hight, low, volume, volume_quota, vibration, rise_fall_rate, rise_fall_quota, turnover_rate)
	VALUES ('{}', {}, {}, {}, {}, {}, {}, {}, {}, {}, {});'''

# 数据库连接参数
## dbname: the database name
## database: the database name (only as keyword argument)
## user: user name used to authenticate
## password: password used to authenticate
## host: database host address (defaults to UNIX socket if not provided)
## port: connection port number (defaults to 5432 if not provided)
conn = psycopg2.connect(database="finance",
                        user="postgres", password="123456",
                        host="127.0.0.1", port="5432")
cur = conn.cursor()  # 创建指针对象
cur.execute("SELECT code, sname FROM stock_market.stock")
datas = cur.fetchall()
# 日期,开盘,收盘,最高,最低,成交量,成交额,振幅,涨跌幅,涨跌额,换手率
for item in datas:
    code = item[0]
    sname = item[1]
    if "ST" in sname:
        print("not create %s", sname)
        continue

    exec_sql = create_table.format("qfq", code)
    csv_file = "./stock_datas/qfq_{}_{}.csv".format(code, sname)
    stock_df = pd.read_csv(csv_file)
    try: 
        print("create table=%s", code)
        cur.execute(exec_sql)
        conn.commit()
        for item in stock_df.values:
            exec_sql = insert_sql.format(code,
                                         item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9], item[10], item[11])
            cur.execute(exec_sql)
        conn.commit()
    except Exception as e:
        print('数据库commit：{}'.format(e))
        sys.exit(1)
